package com.sxj.corejava.code13_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author 石小俊
 */
public class Test04_学生管理系统 {

    public static void main(String[] args) {
        showMenu();
    }

    public static void showMenu() {
        Scanner sc = new Scanner(System.in);
        System.out.println("***********************欢迎使用学生管理系统***********************");
        System.out.print("请选择服务(1.添加 2.删除 3.修改 4.学生列表 5.根据学号查询 6.根据姓名模糊查询 7.根据年龄范围查询 8.退出):");
        int choice = sc.nextInt();
        Student student = null;
        switch (choice) {
            case 1:
                student = new Student();
                System.out.print("请输入学号(唯一):");
                student.setNo(sc.next());
                System.out.print("请输入姓名:");
                student.setName(sc.next());
                System.out.print("请输入年龄:");
                student.setAge(sc.nextInt());
                try {
                    addStudent(student);
                    System.out.println("提示:恭喜您,添加成功");
                } catch (StudentNoExistException e) {
                    System.out.println("错误:对不起,添加失败,失败原因:" + e.getMessage());
                }
                showMenu();
                break;
            case 2:
                System.out.print("请输入需要删除的学生学号:");
                deleteByNo(sc.next());
                showMenu();
                break;
            case 3:
                System.out.print("请输入需要修改的学生学号:");
                String no = sc.next();
                System.out.print("请输入修改后的学生姓名:");
                String name = sc.next();
                System.out.print("请输入修改后的学生年龄:");
                int age = sc.nextInt();
                student = new Student(no, name, age);
                updateByNo(student);
                showMenu();
                break;
            case 4:
                List<Student> students = selectAll();
                System.out.println("编号\t\t学号\t\t姓名\t\t年龄");
                for (Student stu : students) {
                    System.out.println(stu.getId() + "\t\t" + stu.getNo() + "\t\t" + stu.getName() + "\t\t" + stu.getAge());
                }
                showMenu();
                break;
            case 5:
                System.out.print("请输入学号:");
                System.out.println(selectByNo(sc.next()));
                showMenu();
                break;
            case 6:
                System.out.print("请输入姓名:");
                students = selectByName(sc.next());
                System.out.println("编号\t\t学号\t\t姓名\t\t年龄");
                for (Student stu : students) {
                    System.out.println(stu.getId() + "\t\t" + stu.getNo() + "\t\t" + stu.getName() + "\t\t" + stu.getAge());
                }
                showMenu();
                break;
            case 7:
                System.out.print("请输入最小年龄:");
                int minAge = sc.nextInt();
                System.out.print("请输入最大年龄:");
                int maxAge = sc.nextInt();
                students = selectByAge(minAge, maxAge);
                System.out.println("编号\t\t学号\t\t姓名\t\t年龄");
                for (Student stu : students) {
                    System.out.println(stu.getId() + "\t\t" + stu.getNo() + "\t\t" + stu.getName() + "\t\t" + stu.getAge());
                }
                showMenu();
                break;
            case 8:
                System.out.println("提示:您已经退出");
                break;
            case 9:
                System.out.println("错误:您输入的选项有误,请重新输入!");
                showMenu();
                break;
        }
    }

    public static void addStudent(Student student) throws StudentNoExistException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 先判断当前学号是否已经存在
            conn = JDBCUtil.getConnection();
            // 根据学号查询学生信息
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getNo());
            rs = ps.executeQuery();
            if (rs.next()) {
                // 只要存在数据,则表示学号重复了
                throw new StudentNoExistException("学号已经存在了");
            }
            // 如果学号没有重复,则正常添加
            sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student(no,name,age) ")
                    .append(" values(?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getNo());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getAge());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }

    /**
     * 根据学号删除指定学生信息
     * @param no
     */
    public static void deleteByNo(String no) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" delete from t_student ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            ps.executeUpdate();
            System.out.println("提示:恭喜您,删除成功");
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 根据学号修改指定学生信息
     * @param student
     */
    public static void updateByNo(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" update t_student ")
                    .append(" set ")
                    .append("   name = ?, ")
                    .append("   age = ? ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setInt(2, student.getAge());
            ps.setString(3, student.getNo());
            ps.executeUpdate();
            System.out.println("提示:恭喜您,修改成功");
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 查询所有学生信息
     * @return
     */
    public static List<Student> selectAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 根据学号查询指定学生信息
     * @param no
     * @return
     */
    public static Student selectByNo(String no) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            rs = ps.executeQuery();
            while (rs.next()) {
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
            }
            return student;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 根据姓名进行模糊查询
     * @param name
     * @return
     */
    public static List<Student> selectByName(String name) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where name like ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 根据年龄范围查询指定学生信息
     * @param minAge
     * @param maxAge
     * @return
     */
    public static List<Student> selectByAge(int minAge, int maxAge) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where age between ? and ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, minAge);
            ps.setInt(2, maxAge);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }
}
